<?php
/**
 * @package admin
 * @copyright Copyright 2003-2010 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: stats_customers.php 15582 2010-02-26 17:22:10Z ajeh $
 */

require('includes/application_top.php');

function processAttachFileCSV($file_name, $real_name)
{    
    $file = fopen($file_name, "r");
    $msgSuccess = '';
    $msgError = '';
    global $db;    
    $k = 0;
    //pass the first line
    if(!feof($file)) fgets($file);
    while(!feof($file))
    {        
        $line_text = fgets($file);        
        $k = $k + 1;
        if($k < 5) continue;
        $arr = explode(";", $line_text);         
        $products_model = trim($arr[0]);               
        if($products_model !== "")
        {   
            $sql = "select products_id from " . TABLE_PRODUCTS . " 
                    where products_model='" . $products_model . "'";            
            $query = $db->Execute($sql);            
            //if match
            if($query->RecordCount()>0)
            {
                $products_id = $query->fields['products_id'];
                $units = $data->sheets[0]['cells'][$i][2];
                $date_expiration = $data->sheets[0]['cells'][$i][6];
                
                processStock($products_id, $units, $date_expiration, str_replace(".csv", "", $real_name));
                $msgSuccess .= (int)$products_model . ", ";
            }
            else
            {
                $msgError .= (int)$products_model . ", ";  
            }
        }        
    }
    fclose($file);
          
    if($msgSuccess != '') 
    {
        $msgSuccess = substr($msgSuccess, 0, strlen($msgSuccess) - 2);
        $msgSuccess = "Orders $msgSuccess changed status to delivered successful.";
    }
        
    if($msgError != '')
    {
        $msgError = substr($msgError, 0, strlen($msgError) - 2);
        $msgError = "Orders $msgError not existed or the order status not shipped or to be cod.";
    }   
        
    return $msgSuccess . ";" . $msgError;
}


function processAttachFileXLS($file_name, $real_name)
{        
    $msgSuccess = '';
    $msgError = '';
    global $db;    
    $data = new Spreadsheet_Excel_Reader();
    $data->setOutputEncoding('CP1251');
    $data->read($file_name);
    for ($i = 6; $i <= $data->sheets[0]['numRows']; $i++) 
    {        
        $products_model = trim($data->sheets[0]['cells'][$i][1]);        
        if($products_model !== "")
        {   
            $sql = "select products_id from " . TABLE_PRODUCTS . " 
                    where products_model='" . $products_model . "'";            
            $query = $db->Execute($sql);            
            //if match
            if($query->RecordCount()>0)
            {
                $products_id = $query->fields['products_id'];
                $units = $data->sheets[0]['cells'][$i][2];
                $date_expiration = $data->sheets[0]['cells'][$i][6];                
                processStock($products_id, $units, $date_expiration, str_replace(".xls", "", $real_name));
                $msgSuccess .= $products_model . ", ";
            }
            else
            {
                $msgError .= $products_model . ", ";  
            }
        }        
    }     
    if($msgSuccess != '') 
    {
        $msgSuccess = substr($msgSuccess, 0, strlen($msgSuccess) - 2);
        $msgSuccess = "Products $msgSuccess updated successful.";
    }
        
    if($msgError != '')
    {
        $msgError = substr($msgError, 0, strlen($msgError) - 2);
        $msgError = "Products $msgError not existed or the format incorrect.";
    }   
        
    return $msgSuccess . ";" . $msgError;
}

function processAttachFileXLSX($file_name, $real_name)
{        
    $msgSuccess = '';
    $msgError = '';
    global $db;    
    $xlsx = new SimpleXLSX($file_name);    
    foreach( $xlsx->rows() as $k => $r) {
        if ($k < 5) continue; // skip first row        
        $products_model = trim($r[0]);                
        if($products_model !== "")
        {   
            $sql = "select products_id from " . TABLE_PRODUCTS . " 
                    where products_model='" . $products_model . "'";            
            $query = $db->Execute($sql);            
            //if match
            if($query->RecordCount()>0)
            {
                $products_id = $query->fields['products_id'];
                $units = $r[1];
                $date_expiration = $r[5];                
                processStock($products_id, $units, $date_expiration, str_replace(".xlsx", "", $real_name));
                $msgSuccess .= $products_model . ", ";
            }
            else
            {
                $msgError .= $products_model . ", ";  
            }
        }        
    }          
    if($msgSuccess != '') 
    {
        $msgSuccess = substr($msgSuccess, 0, strlen($msgSuccess) - 2);
        $msgSuccess = "Products $msgSuccess updated successful.";
    }
        
    if($msgError != '')
    {
        $msgError = substr($msgError, 0, strlen($msgError) - 2);
        $msgError = "Products $msgError not existed or the format incorrect.";
    }   
        
    return $msgSuccess . ";" . $msgError;
}

function processStock($products_id, $units, $date_expiration, $file_name)
{
    global $db;
    $db->Execute("update " . TABLE_PRODUCTS . " set products_quantity = products_quantity + " . $units . " where products_id='" . $products_id . "'");        
    
    if((int)$date_expiration > 0)
    {
        $timestamp = ((int)$date_expiration - 25569) * 24 * 60 * 60;
        $date_expiration = date('Y-m-d', $timestamp);
        //echo $date_expiration;exit;
        
        $db->Execute("update " . TABLE_PRODUCTS . " 
            set products_expiration_date = '" . $date_expiration . "' 
            where products_id='" . $products_id . "'
            and (products_expiration_date is null 
                    or TIMESTAMPDIFF(MONTH , '" . $date_expiration . "', products_expiration_date) > 0)");        
    }
    $products = $db->Execute("Select products_quantity from " . TABLE_PRODUCTS . " where products_id='" . $products_id . "'");
    if(!$products->EOF)
    {
        $db->Execute("insert into stock_movements (products_id, movements_units, stock_after, admin_id, movements_date, movements_comment, movements_type)
            values (
                '" . $products_id . "', 
                '" . $units . "',
                '" . (int)$products->fields['products_quantity'] . "',
                '" . $_SESSION['admin_id'] . "',
                now(),
                '" . $file_name . "',
                'excel_upload'
            )");
    }
}



require(DIR_WS_CLASSES . 'currencies.php');
$currencies = new currencies();
if (isset($_POST['Upload']))
{
    $message = "";            
    if(isset($_FILES["attach_file"]["name"]))
    {        
        $ext = end(explode(".", strtolower($_FILES["attach_file"]["name"])));
        if($ext === "csv")
            $message = processAttachFileCSV($_FILES["attach_file"]["tmp_name"], strtolower($_FILES["attach_file"]["name"]));
        else if($ext === "xls")
        {            
            require_once('includes/templates/Excel/reader.php');
            $message = processAttachFileXLS($_FILES["attach_file"]["tmp_name"], strtolower($_FILES["attach_file"]["name"]));            
        }
        else if($ext === "xlsx")
        {            
            require_once('includes/templates/Excel/simplexlsx.class.php');
            $message = processAttachFileXLSX($_FILES["attach_file"]["tmp_name"], strtolower($_FILES["attach_file"]["name"]));            
        }
        $arrMsg = explode(";", $message);        
        if($arrMsg[0] != '') $messageStack->add($arrMsg[0], 'success');
        if($arrMsg[1] != '') $messageStack->add($arrMsg[1], 'error');
    }
    else
        $message = "Please input the file name";
}

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript" src="/jquery-1.7.1.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
</head>
<body onload="init()">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<div style="margin-top: 30px; text-align: center">
    <form action="update_stock.php" method="post" enctype="multipart/form-data">        
        <div>
            <input type="file" name="attach_file" size="50" />
            &nbsp;<input type="submit" value="Upload" name="Upload" />
        </div>
    </form>
</div>
<table style="margin-left:auto; margin-right:auto; margin-top: 40px; margin-bottom:30px;">

<tr align="center" style=" font:'Trebuchet MS', Arial, Helvetica, sans-serif; background: #0CF; font-weight:bold; text-decoration:underline;"> <td style="padding:10px 20px 10px 20px;">ID</td> <td style="padding:10px 20px 10px 20px;">Product Name</td> <td style="padding:10px 20px 10px 20px;">Model Name</td> <td style="padding:10px 20px 10px 20px;">Product In Stock</td> <td style="padding:10px 20px 10px 20px;">Product Price</td> <td style="padding:10px 20px 10px 20px;">Product Expiration Date</td><td style="padding:10px 20px 10px 20px;">Comment</td> <td style="padding:10px 20px 10px 20px;">Update Stock</td></tr>
<?php

$sql=("SELECT distinct products.products_id, products.products_quantity, products.products_model, products.products_price
        , date_format(products.products_expiration_date, '%m/%Y') as products_expiration_date,products_description.products_name
    FROM products INNER JOIN products_description ON products.products_id=products_description.products_id and products_description.language_id='" . $_SESSION['languages_id'] . "'        
    ORDER BY products_name");

$result=mysql_query($sql);
$num=mysql_num_rows($result);

$i=0;
while ($i < $num) {
	$id=mysql_result($result,$i,"products_id");
	$products_name=mysql_result($result,$i,"products_name");
	$products_quantity=mysql_result($result,$i,"products_quantity");
	$products_model=mysql_result($result,$i,"products_model");
	$products_price=mysql_result($result,$i,"products_price");
	$products_expiration_date=mysql_result($result,$i,"products_expiration_date");
?>

<tr id="trt_products_ids_<?php echo $id;?>">

<td style="text-align:center; background:#4B7E4B; color:#FFF;"> <input type="hidden" name="ud_id" value="<?php echo $id; ?>"><?php echo $id; ?></td>

<td><input type="text" name="ud_products_name" onkeydown="enter(event, '<?php echo $id;?>')" value="<?php echo $products_name;?>"></td>

<td><input type="text" name="ud_products_model" onkeydown="enter(event, '<?php echo $id;?>')" value="<?php echo $products_model;?>"></td>

<td><input type="text" name="ud_products_quantity" onkeydown="enter(event, '<?php echo $id;?>')" value="<?php echo $products_quantity;?>">
    <input type="hidden" name="ud_hidden_products_quantity" value="<?php echo $products_quantity;?>" />
</td>

<td><input type="text" name="ud_products_price" onkeydown="enter(event, '<?php echo $id;?>')" value="<?php echo $products_price;?>"></td>

<td><input type="text" name="ud_products_expiration_date" onkeydown="enter(event, '<?php echo $id;?>')" value="<?php echo $products_expiration_date;?>"></td>

<td><input type="text" name="ud_products_movements_comment" onkeydown="enter(event, '<?php echo $id;?>')" value=""></td>

<td style="text-align:center;"><input type="button" name="Update" onclick="update('<?php echo $id;?>')" value="Update"></td>

</tr>

<?php
++$i;
}
?>
</table>
<script type="text/javascript">
	function update(ud_id)
	{
		var ud_products_name = $("#trt_products_ids_" + ud_id).find("[name=ud_products_name]").val();
		var ud_products_model = $("#trt_products_ids_" + ud_id).find("[name=ud_products_model]").val();
		var ud_products_quantity = $("#trt_products_ids_" + ud_id).find("[name=ud_products_quantity]").val();
                var ud_hidden_products_quantity = $("#trt_products_ids_" + ud_id).find("[name=ud_hidden_products_quantity]").val();
		var ud_products_price = $("#trt_products_ids_" + ud_id).find("[name=ud_products_price]").val();
		var ud_products_expiration_date = $("#trt_products_ids_" + ud_id).find("[name=ud_products_expiration_date]").val();
                var ud_products_movements_comment = $("#trt_products_ids_" + ud_id).find("[name=ud_products_movements_comment]").val();
		//blur button update
		var btn = $("#trt_products_ids_" + ud_id).find("[name=Update]");
		btn.attr('disabled', 'disabled');
		$.ajax({
            type: "POST",
            async: false,
            url: 'ajax_update_stock.php',
            data: {ud_id: ud_id, ud_products_name: ud_products_name, ud_products_model: ud_products_model
                , ud_products_quantity: ud_products_quantity, ud_hidden_products_quantity: ud_hidden_products_quantity
                , ud_products_price: ud_products_price
                , ud_products_expiration_date: ud_products_expiration_date, ud_products_movements_comment: ud_products_movements_comment},
            success: function (data) {
                if (data == "SUCCESS") {				                
                    //alert("Updated succesful.");
                    $("#trt_products_ids_" + ud_id).find("[name=ud_hidden_products_quantity]").val(ud_products_quantity);
                    $("#trt_products_ids_" + ud_id).find("[name=ud_products_movements_comment]").val('');
                    btn.removeAttr('disabled');
                }
                else
                    alert(data);
            }
        });
	}

	function enter(e, ud_id)
	{
		var key = e.keyCode ? e.keyCode : e.which ? e.which : e.charCode;
		if(key == 13)
		{
			update(ud_id);
		}
	}
</script>
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>







